
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.*;
import java.text.*;

public class Database_access {

    private static Connection con = null;
    private static Statement statement = null;
    private static PreparedStatement preparedStatement = null;
    private static ResultSet resultSet = null;

   

    public Database_access() throws Exception {
        // This will load the MySQL driver, each DB has its own driver
        Class.forName("com.mysql.jdbc.Driver");
        // Setup the connection with the DB
        con = DriverManager.getConnection("jdbc:mysql://localhost/ivrs", "root", "");
    }

    public int login(String name, String pass) throws Exception {
        
        statement = con.createStatement();
        // statement.executeUpdate("INSERT INTO table_login (username, password) VALUES ('"+name+"', '"+pass+"')");
        // Result set get the result of the SQL query
        resultSet = statement.executeQuery("select * from ivrs.Table_Studentdata where Col_username='" + name + "' and Col_pin ='" + pass + "' ");

        

        resultSet.last();
        int temp = resultSet.getRow();

        if (temp == 1) {
            return 1;
        }

        resultSet = statement.executeQuery("select * from ivrs.Table_Facultydata where Col_username='" + name + "' and Col_password ='" + pass + "' ");
             resultSet.last();
        temp = resultSet.getRow();
        if (temp == 1) {
            

            return 2;
        }

        return 0;

    }

    public int StudentReg(String name, String mobno, int pin, String rollno) throws Exception {

        statement = con.createStatement();
        
        String email_name[]=name.split(" ");
        String email_roll=rollno.substring(2);
        
        String email_id=email_name[0]+email_roll+"@iiitd.ac.in";
        
        

        statement.executeUpdate("INSERT INTO Table_Studentdata (Col_username, Col_studentname, Col_mobileNo, Col_pin, Col_rollno) VALUES ('"+email_id+"', '" + name + "', '" + mobno + "', '" + pin + "', '" + rollno + "')");
        
        Email_Pin ob=new Email_Pin();
        
        String PinNo=pin+"";
        
        ob.sendmail(name, email_id, PinNo);
        return 0;
    }

    public int FacultyReg(String email, String name, String password, String mobno)
            throws Exception {
        statement = con.createStatement();
        statement.executeUpdate("INSERT INTO Table_Facultydata (Col_username, Col_Facultyname, Col_password, Col_Mobileno) VALUES ('" + email + "', '" + name + "', '" + password + "', '" + mobno + "')");
        return 0;

    }

    void InsertMarkSheet(String[][] values, String tablename)throws Exception
    {
         statement = con.createStatement();
         statement.execute("DROP TABLE IF EXISTS "+tablename);
         statement.execute("CREATE TABLE `ivrs`.`"+tablename+"` (  `col_rollnumber` VARCHAR(20) NOT NULL ,  `col_marks` VARCHAR(10) NULL ,  PRIMARY KEY (`col_rollno`) )");

         //System.out.println(values.length);
         for(int i=0; i<values.length; i++)
         {
             
         String rollno=values[i][0];
         String marks=values[i][1];
        // System.out.println(rollno+"  "+ marks);
         statement.executeUpdate("INSERT INTO "+tablename+ " VALUES ('"+rollno+"', '"+marks+"')");
        }
    }

    void WriteSMSinDatabase(String message) throws Exception
    {
        statement = con.createStatement();

        DateFormat Format_date= new SimpleDateFormat("MMM d yyyy");
        DateFormat Format_time= new SimpleDateFormat("HH:mm:ss");

        System.out.println(GetLoginDetails.faculty_userID);

        Date ob1= new Date();

        String date=Format_date.format(ob1);
        String time=Format_time.format(ob1);
        String username=GetLoginDetails.faculty_userID;

        statement.executeUpdate("INSERT INTO `ivrs`.`table_sms` (`Col_username`, `Col_message`, `Col_date`, `Col_time`) VALUES ('"+username+"', '"+message+"', '"+date+"', '"+time+"')");



    }
}
